
import pandas as pd
from math import radians, sin, cos, sqrt, atan2


def calculate_distance(lat1, lon1, lat2, lon2):
    # 地球平均半径，单位为米
    R = 6371000
    lat1_rad = radians(lat1)
    lon1_rad = radians(lon1)
    lat2_rad = radians(lat2)
    lon2_rad = radians(lon2)

    dlat = lat2_rad - lat1_rad
    dlon = lon2_rad - lon1_rad

    a = sin(dlat / 2)**2 + cos(lat1_rad) * cos(lat2_rad) * sin(dlon / 2)**2
    c = 2 * atan2(sqrt(a), sqrt(1 - a))

    distance = R * c
    return distance


# 假设Excel文件路径和sheet名称如下
file_path = r'D:\xlzx\副本3月联通反馈电信经纬度偏差问题小区20250310.xlsx'  # 更新为新的文件目录
sheet1_name = 'Sheet1'  # 替换为实际的sheet名称
sheet2_name = 'Sheet2'  # 替换为实际的sheet名称

# 读取Excel文件中的数据
df1 = pd.read_excel(file_path, sheet_name=sheet1_name)
df2 = pd.read_excel(file_path, sheet_name=sheet2_name)

# 计算距离并保存结果
distances = []
for index, row in df1.iterrows():
    lat1 = row['RRU经度(WGS84)']
    lon1 = row['RRU纬度(WGS84)']
    lat2 = df2.loc[index, 'RRU经度(铁塔)']
    lon2 = df2.loc[index, 'RRU纬度(铁塔)']
    distance = calculate_distance(lat1, lon1, lat2, lon2)
    distances.append(distance)

df1['Distance'] = distances

# 将结果写回Excel文件
with pd.ExcelWriter(file_path, engine='openpyxl', mode='a') as writer:
    df1.to_excel(writer, sheet_name=sheet1_name, index=False)

